﻿CREATE VIEW [dbo].[AndrewsStandardSailView]
AS
SELECT     OEMSailListID, BoatGenID,
                          (SELECT     Manufacturer
                            FROM          dbo.GenBoatData_Tbl
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS Manufacturer,
                          (SELECT     BoatRange
                            FROM          dbo.GenBoatData_Tbl AS GenBoatData_Tbl_4
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS BoatRange,
                          (SELECT     BoatModel
                            FROM          dbo.GenBoatData_Tbl AS GenBoatData_Tbl_3
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS BoatModel,
                          (SELECT     BoatType
                            FROM          dbo.GenBoatData_Tbl AS GenBoatData_Tbl_2
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS BoatType,
                          (SELECT     Loa
                            FROM          dbo.GenBoatData_Tbl AS GenBoatData_Tbl_1
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS LOA, OEMIdentifier, CustomerID, MarketID,
                          (SELECT     Market
                            FROM          dbo.Market_Tbl
                            WHERE      (MarketID = dbo.OEMSailList_Tbl.MarketID)) AS Market, Area, QuoteSailTypeID,
                          (SELECT     SailType
                            FROM          dbo.QuoteSailType_Tbl
                            WHERE      (QuoteSailTypeID = dbo.OEMSailList_Tbl.QuoteSailTypeID)) AS SailType, GenericYarnID,
                          (SELECT     YarnType
                            FROM          dbo.GenericYarn_Tbl
                            WHERE      (GenericYarnID = dbo.OEMSailList_Tbl.GenericYarnID)) AS YarnType, FilmTypeID,
                          (SELECT     FilmType
                            FROM          dbo.FilmType_Tbl
                            WHERE      (FilmTypeID = dbo.OEMSailList_Tbl.FilmTypeID)) AS FilmType, DPI,
                          (SELECT     TargetDPI
                            FROM          dbo.TargetDPI_Tbl
                            WHERE      (TargetDPIID = dbo.OEMSailList_Tbl.DPI)) AS TargetDPI, CurrencyID,
                          (SELECT     Currency
                            FROM          dbo.Currency_Tbl
                            WHERE      (CurrencyID = dbo.OEMSailList_Tbl.CurrencyID)) AS Currency, TierID,
                          (SELECT     Tier
                            FROM          dbo.Tier_Tbl
                            WHERE      (TierID = dbo.OEMSailList_Tbl.TierID)) AS Tier, UsageID,
                          (SELECT     UsageType
                            FROM          dbo.Usage_Tbl
                            WHERE      (UsageTypeID = dbo.OEMSailList_Tbl.UsageID)) AS UsageType, Createdate, ConstructionID,
                          (SELECT     Construction
                            FROM          dbo.Construction_Tbl
                            WHERE      (ConstructionID = dbo.OEMSailList_Tbl.ConstructionID)) AS Construction, MarketRetailPrice, ListRetailPrice, OEMRetailPrice, Cut, Weight, 
                      AccessoryID,
                          (SELECT DISTINCT Accessory
                            FROM          dbo.Accessory_Tbl
                            WHERE      (AccessoryID = dbo.OEMSailList_Tbl.AccessoryID)) AS Accessory, SailNumberColourID,
                          (SELECT     Colour
                            FROM          dbo.Colour_Tbl
                            WHERE      (ColourID = dbo.OEMSailList_Tbl.SailNumberColourID)) AS Colour, FabricID,
                          (SELECT     PartCode
                            FROM          dbo.Fabric_Tbl
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS Fabric, NotionalArea, TopSurfaceID,
                          (SELECT     SurfaceComponent
                            FROM          dbo.SurfaceComponent_Tbl
                            WHERE      (SurfaceComponentID = dbo.OEMSailList_Tbl.TopSurfaceID)) AS TopSurfaceComponent, BotSurfaceID,
                          (SELECT     SurfaceComponent
                            FROM          dbo.SurfaceComponent_Tbl AS SurfaceComponent_Tbl_1
                            WHERE      (SurfaceComponentID = dbo.OEMSailList_Tbl.BotSurfaceID)) AS Expr1, GenSailTypeID,
                          (SELECT     GenSailType
                            FROM          dbo.GenSailType_Tbl
                            WHERE      (GenSailTypeID = dbo.OEMSailList_Tbl.GenSailTypeID)) AS GenSailType, Notes, OptionID,
                          (SELECT     OptionName
                            FROM          dbo.Option_Tbl
                            WHERE      (OptionID = dbo.OEMSailList_Tbl.OptionID)) AS OptionName, DesignFile, Standard, QuoteLineItemID, QuoteID, LineItem, SailModelCode, 
                      CustomerIdentifier, AttachedToLineItem, HasAttachment, AxaptaCode
FROM         dbo.OEMSailList_Tbl